/*
 Navicat Premium Dump SQL

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50744 (5.7.44)
 Source Host           : 127.0.0.1:3306
 Source Schema         : smart_community

 Target Server Type    : MySQL
 Target Server Version : 50744 (5.7.44)
 File Encoding         : 65001

 Date: 08/09/2025 15:44:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sc_owner_household
-- ----------------------------
DROP TABLE IF EXISTS `sc_owner_household`;
CREATE TABLE `sc_owner_household` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `owner_id` bigint(20) NOT NULL COMMENT '业主ID',
  `household_id` bigint(20) NOT NULL COMMENT '房户ID',
  `ownership_type` tinyint(4) DEFAULT '1' COMMENT '产权类型：1-完全产权，2-共有产权，3-部分产权',
  `ownership_ratio` decimal(5,2) DEFAULT '100.00' COMMENT '产权比例（%）',
  `ownership_start_date` date DEFAULT NULL COMMENT '产权开始日期',
  `ownership_end_date` date DEFAULT NULL COMMENT '产权结束日期（适用于部分产权）',
  `is_primary_owner` tinyint(4) DEFAULT '0' COMMENT '是否主业主：0-否，1-是',
  `certificate_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '房产证号',
  `certificate_date` date DEFAULT NULL COMMENT '房产证日期',
  `mortgage_info` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '抵押信息',
  `remark` text COLLATE utf8mb4_unicode_ci COMMENT '备注信息',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0-禁用，1-启用（遵循SystemStatusEnum标准）',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人ID',
  `version` int(11) DEFAULT '1' COMMENT '乐观锁版本号',
  `association_status` tinyint(4) DEFAULT '1' COMMENT '关联状态：0-待确认，1-已确认，2-已解除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_owner_household` (`owner_id`,`household_id`),
  KEY `idx_household_id` (`household_id`),
  KEY `idx_ownership_type` (`ownership_type`),
  KEY `idx_is_primary_owner` (`is_primary_owner`),
  KEY `idx_status` (`status`),
  KEY `idx_owner_household_owner_status` (`owner_id`,`status`),
  KEY `idx_owner_household_household_status` (`household_id`,`status`),
  KEY `idx_owner_household_ownership` (`ownership_type`,`is_primary_owner`),
  KEY `idx_association_status` (`association_status`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `sc_owner_household_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `sc_owner` (`id`),
  CONSTRAINT `sc_owner_household_ibfk_2` FOREIGN KEY (`household_id`) REFERENCES `sc_household` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='业主房户关联表（关联表不使用软删除，保留历史记录）';

-- ----------------------------
-- Records of sc_owner_household
-- ----------------------------
BEGIN;
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (14, 1, 10, 1, 100.00, '2020-01-01', NULL, 1, '京房权证字第001号', '2020-01-01', '无抵押', '完全产权', 1, '2025-08-21 03:30:55', '2025-08-21 03:30:55', 1, 1, 1, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (15, 1, 11, 1, 100.00, '2020-02-01', NULL, 1, '京房权证字第002号', '2020-02-01', '无抵押', '完全产权', 1, '2025-08-21 03:30:55', '2025-08-21 03:30:55', 1, 1, 1, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (16, 2, 12, 2, 50.00, '2020-03-01', NULL, 0, '京房权证字第003号', '2020-03-01', '无抵押', '共有产权', 1, '2025-08-21 03:30:55', '2025-08-21 03:30:55', 1, 1, 1, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (17, 3, 13, 1, 100.00, '2020-04-01', NULL, 1, '京房权证字第004号', '2020-04-01', '无抵押', '完全产权', 1, '2025-08-21 03:30:55', '2025-08-21 03:30:55', 1, 1, 1, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (18, 4, 14, 3, 80.00, '2020-05-01', NULL, 1, '京房权证字第005号', '2020-05-01', '部分抵押', '部分产权', 1, '2025-08-21 03:30:55', '2025-08-21 03:30:55', 1, 1, 1, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (19, 7, 364, 1, 100.00, NULL, NULL, 1, '111111111111111111111111', '2025-08-01', NULL, NULL, 1, '2025-08-22 17:22:43', '2025-08-24 19:02:33', 35, 35, 2, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (20, 7, 121, 1, 100.00, NULL, NULL, 1, '5065798005467980545', '2025-08-01', '112222', NULL, 1, '2025-08-24 14:58:10', '2025-08-24 15:43:06', 35, 35, 3, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (21, 7, 172, 2, 50.00, NULL, NULL, 1, '222222222222222222', '2025-08-25', NULL, NULL, 1, '2025-08-24 19:03:31', '2025-08-25 11:20:52', 35, 35, 2, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (22, 8, 263, 1, 100.00, NULL, NULL, 1, '4264574568845617987080', '2025-08-01', NULL, NULL, 1, '2025-08-24 19:38:09', '2025-08-26 14:43:01', 35, 35, 2, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (23, 8, 757, 1, 100.00, NULL, NULL, 1, '10101010110101010101010101010101', '2025-08-01', NULL, NULL, 1, '2025-08-28 15:10:25', '2025-08-28 15:11:21', 42, 42, 2, 1);
INSERT INTO `sc_owner_household` (`id`, `owner_id`, `household_id`, `ownership_type`, `ownership_ratio`, `ownership_start_date`, `ownership_end_date`, `is_primary_owner`, `certificate_number`, `certificate_date`, `mortgage_info`, `remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `version`, `association_status`) VALUES (24, 8, 751, 1, 100.00, NULL, NULL, 1, '212123121212112121212121212121212121', '2025-09-01', 'remark', NULL, 1, '2025-09-04 16:32:21', '2025-09-04 16:32:21', 42, 42, 1, 1);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
